﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Lihong.Cms.Entity;
using MySql.Data.MySqlClient;

//1.项目右键 ，管理nuget程序包
//2.nuget里找到mysql.data，添加引用
//3.引用命名空间 MySql.Data.MySqlClient
//4.开始写代码连接数据库

namespace Lihong.Cms.Data
{
    public class NewsMySqlRepository : IBaseData<News>
    {
        public bool Add(News u)
        {
            // 2.insert
            throw new NotImplementedException();
        }

        public bool Delete(int id)
        {
            // 4.delete
            MySqlConnection con = new MySqlConnection();
            con.ConnectionString = "server=localhost;port=3306;database=cms;uid=root;pwd=root;";
            con.Open();

            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = con;
            cmd.CommandText = @"delete from news where id=@id";
            //自己补全
            cmd.Parameters.AddWithValue("@id", id);

            cmd.ExecuteNonQuery();

            con.Close();

            return true;
        }

        public News Get(int id)
        {
            // 1.select
            MySqlConnection con = new MySqlConnection();
            con.ConnectionString = "server=localhost;port=3306;database=cms;uid=root;pwd=root;";
            con.Open();

            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "select * from news where id=@id";
            cmd.Parameters.AddWithValue("@id", id);

            MySqlDataReader reader = cmd.ExecuteReader();
            reader.Read();

            News entity = new News(); ;
            entity.Id = (int)reader["Id"];
            entity.CategoryId = (int)reader["CategoryId"];
            entity.CreatedOn = (DateTime)reader["CreatedOn"];
            entity.Title = (string)reader["Title"];
            entity.Content = (string)reader["Content"];
            entity.Author = (string)reader["Author"];
            entity.Source = (string)reader["Source"];
            entity.ViewCount = (int)reader["ViewCount"];
            entity.Picture = (string)reader["Picture"];

            con.Close();

            return entity;
        }

        public ICollection<News> GetAll()
        {
            // 1.select
            MySqlConnection con = new MySqlConnection();
            con.ConnectionString = "server=localhost;port=3306;database=cms;uid=root;pwd=root;";
            con.Open();

            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "select * from news";

            MySqlDataReader reader = cmd.ExecuteReader();

            List<News> list = new List<News>();
            while (reader.Read())
            {
                News entity = new News(); ;
                entity.Id = (int)reader["Id"];
                entity.CategoryId = (int)reader["CategoryId"];
                entity.CreatedOn = (DateTime)reader["CreatedOn"];
                entity.Title = (string)reader["Title"];
                entity.Content = (string)reader["Content"];
                entity.Author = (string)reader["Author"];
                entity.Source = (string)reader["Source"];
                entity.ViewCount = (int)reader["ViewCount"];
                entity.Picture = (string)reader["Picture"];
                list.Add(entity);
            }

            con.Close();

            return list;
        }

        public void Save()
        {
        }

        public bool Update(News u)
        {
            // sql: update
            MySqlConnection con = new MySqlConnection();
            con.ConnectionString = "server=localhost;port=3306;database=cms;uid=root;pwd=root;";
            con.Open();

            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = con;
            cmd.CommandText = @"update news set 
ViewCount=@ViewCount 
where id=@id";
            //自己补全
            cmd.Parameters.AddWithValue("@id", u.Id);
            cmd.Parameters.AddWithValue("@ViewCount", u.ViewCount);

            cmd.ExecuteNonQuery();

            con.Close();

            return true;
        }
    }
}
